set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;


insert overwrite table jms_dm.dm_whole_decide_duty_pres_base_dt
select 
     main.waybill_no                         --运单号
    ,main.platform_effect                    --自有平台时效标准(兜底路由)
    ,main.ziyou_platform_effect              --自有平台时效标准(推荐路由)
    ,main.if_platform_standard               --自有平台时效是否达成:1达成,0未达成(推荐路由)
    ,main.if_doudi_platform_standard         --自有平台时效是否达成:1达成,0未达成(兜底路由)
    ,main.cainiao_platform_effect            --七星潭平台时效标准
    ,main.if_cainiao_platform_pres_standard  --七星潭平台时效是否达成:1达成,0未达成
    ,main.order_source_code                  --订单来源编码
    ,main.order_source_name                  --订单来源名称   
    ,case when main.duty_main_type in (5,6) and main.duty_type = '建包错误' then '收环节-建包错误'
          when main.duty_main_type = 4      and main.duty_type = '建包错误' then '中转环节-建包错误'
          when main.duty_main_type = 4      and main.duty_type = '错分'     then '中转环节-错分'
          when main.duty_main_type in (5,6) and main.duty_type = '错分'     then '派环节-错分'
          else main.duty_type end as duty_type  --七星潭责任类型           
    ,main.duty_main_code                    as duty_main_code                    --七星潭责任主体编码              
    ,main.duty_main_name                    as duty_main_name                    --七星潭责任主体名称              
    ,main.duty_main_type                    as duty_main_type                    --七星潭责任主体类型   
    ,dim_qxt.fran_code                      as duty_main_fran_code               --七星潭责任加盟商编码
    ,dim_qxt.fran_name                      as duty_main_fran_name               --七星潭责任加盟商名称
    ,dim_qxt.agent_code                     as duty_main_agent_code              --七星潭责任代理区编码
    ,dim_qxt.agent_name                     as duty_main_agent_name              --七星潭责任代理区名称
    ,dim_qxt.provider_id                    as duty_provider_code                --七星潭责任省份编码
    ,dim_qxt.provider_desc                  as duty_provider_desc                --七星潭责任省份名称
    ,main.duty_shipment_no                  as duty_shipment_no                  --七星潭责任任务号               
    ,main.duty_shipment_name                as duty_shipment_name                --七星潭责任任务号名称             
    ,main.qxt_dynamic_scan_time             as qxt_dynamic_scan_time             --七星潭实际扫描时间              
    ,main.qxt_static_scan_time              as qxt_static_scan_time              --七星潭静态扫描时间              
    ,main.qxt_scan_site_actual_arrival_time as qxt_scan_site_actual_arrival_time --七星潭当前站点实际到车时间          
    ,main.qxt_scan_site_plan_arrival_time   as qxt_scan_site_plan_arrival_time   --七星潭当前站点规划到车时间  
    ,case when main.ziyou_duty_main_type in (5,6) and main.ziyou_duty_type = '建包错误' then '收环节-建包错误'
          when main.ziyou_duty_main_type = 4      and main.ziyou_duty_type = '建包错误' then '中转环节-建包错误'
          when main.ziyou_duty_main_type = 4      and main.ziyou_duty_type = '错分'     then '中转环节-错分'
          when main.ziyou_duty_main_type in (5,6) and main.ziyou_duty_type = '错分'     then '派环节-错分'
          else main.ziyou_duty_type end as ziyou_duty_type  --自有平台责任类型
    ,main.ziyou_duty_main_code             as ziyou_duty_main_code              --自有平台责任主体编码 
    ,main.ziyou_duty_main_name             as ziyou_duty_main_name              --自有平台责任主体名称 
    ,main.ziyou_duty_main_type             as ziyou_duty_main_type              --自有平台责任主体类型 
    ,dim_zy.fran_code                      as ziyou_duty_main_fran_code         --自有平台责任加盟商编码
    ,dim_zy.fran_name                      as ziyou_duty_main_fran_name         --自有平台责任加盟商名称
    ,dim_zy.agent_code                     as ziyou_duty_main_agent_code        --自有平台责任代理区编码
    ,dim_zy.agent_name                     as ziyou_duty_main_agent_name        --自有平台责任代理区名称
    ,dim_zy.provider_id                    as ziyou_duty_provider_code          --自有平台责任省份编码
    ,dim_zy.provider_desc                  as ziyou_duty_provider_desc          --自有平台责任省份名称
    ,main.ziyou_duty_shipment_no           as ziyou_duty_shipment_no            --自有平台责任任务号
    ,main.ziyou_duty_shipment_name         as ziyou_duty_shipment_name          --自有平台责任任务号名称
    ,main.zy_dynamic_scan_time             as zy_dynamic_scan_time              --自有平台实际扫描时间
    ,main.zy_static_scan_time              as zy_static_scan_time               --自有平台静态扫描时间
    ,main.zy_scan_site_actual_arrival_time as zy_scan_site_actual_arrival_time  --自有平台当前站点实际到车时间
    ,main.zy_scan_site_plan_arrival_time   as zy_scan_site_plan_arrival_time    --自有平台当前站点规划到车时间
    ,dim_start.code                        as start_network_code                --始发网点编码
    ,dim_start.name                        as start_network_name                --始发网点名称
    ,dim_start.city_id                     as start_city_code                   --始发城市编码
    ,dim_start.city_desc                   as start_city_name                   --始发城市名称
    ,dim_start.provider_id                 as start_provider_code               --始发省份编码
    ,dim_start.provider_desc               as start_provider_name               --始发省份名称
    ,dim_start.fran_code                   as start_fran_code                   --始发加盟商编码
    ,dim_start.fran_name                   as start_fran_name                   --始发加盟商名称
    ,dim_start.agent_code                  as start_agent_code                  --始发代理区编码
    ,dim_start.agent_name                  as start_agent_name                  --始发代理区名称
    ,main.start_center_code                as start_center_code                 --始发中心编码
    ,main.start_center_name                as start_center_name                 --始发中心名称
    ,dim_end.code                          as end_network_code                  --目的网点编码
    ,dim_end.name                          as end_network_name                  --目的网点名称
    ,dim_end.city_id                       as end_city_code                     --目的城市编码
    ,dim_end.city_desc                     as end_city_name                     --目的城市名称
    ,dim_end.provider_id                   as end_provider_code                 --目的省份编码
    ,dim_end.provider_desc                 as end_provider_name                 --目的省份名称
    ,dim_end.fran_code                     as end_fran_code                     --目的加盟商编码
    ,dim_end.fran_name                     as end_fran_name                     --目的加盟商名称
    ,dim_end.agent_code                    as end_agent_code                    --目的代理区编码
    ,dim_end.agent_name                    as end_agent_name                    --目的代理区名称
    ,main.end_center_code                  as end_center_code                   --目的中心编码
    ,main.end_center_name                  as end_center_name                   --目的中心名称
    ,main.cainiao_plan_sign_time           as cainiao_plan_sign_time            --菜鸟平台规划签收时间
    ,main.ziyou_plan_sign_time             as ziyou_plan_sign_time              --自有平台规划签收时间
    ,main.aging_sign_time                  as aging_sign_time                   --时效签收时间
    ,main.actual_sign_time                 as actual_sign_time                  --实际签收时间
    ,main.customer_code                    as customer_code                     --客户编码  20230824新增
    ,main.customer_name                    as customer_name                     --客户名称
    ,main.duty_main_bagg_user_code         as duty_main_bagg_user_code          --七星潭责任主体建包扫描员编号
    ,main.duty_main_bagg_user_name         as duty_main_bagg_user_name          --七星潭责任主体建包扫描员名称
    ,main.duty_main_pre_site_code          as duty_main_pre_site_code           --七星潭责任主体上一站编码
    ,main.duty_main_pre_site_name          as duty_main_pre_site_name           --七星潭责任主体上一站名称
    ,main.duty_main_pre_site_type          as duty_main_pre_site_type           --七星潭责任主体上一站类型
    ,main.duty_main_next_site_code         as duty_main_next_site_code          --七星潭责任主体下一站编码
    ,main.duty_main_next_site_name         as duty_main_next_site_name          --七星潭责任主体下一站名称
    ,main.duty_main_next_site_type         as duty_main_next_site_type          --七星潭责任主体下一站类型
    ,main.ziyou_duty_main_bagg_user_code   as ziyou_duty_main_bagg_user_code    --自有平台责任主体建包扫描员编号    
    ,main.ziyou_duty_main_bagg_user_name   as ziyou_duty_main_bagg_user_name    --自有平台责任主体建包扫描员名称    
    ,main.ziyou_duty_main_pre_site_code    as ziyou_duty_main_pre_site_code     --自有平台责任主体上一站编码
    ,main.ziyou_duty_main_pre_site_name    as ziyou_duty_main_pre_site_name     --自有平台责任主体上一站名称
    ,main.ziyou_duty_main_pre_site_type    as ziyou_duty_main_pre_site_type     --自有平台责任主体上一站类型
    ,main.ziyou_duty_main_next_site_code   as ziyou_duty_main_next_site_code    --自有平台责任主体下一站编码
    ,main.ziyou_duty_main_next_site_name   as ziyou_duty_main_next_site_name    --自有平台责任主体下一站名称
    ,main.ziyou_duty_main_next_site_type   as ziyou_scan_site_next_site_type    --自有平台责任主体下一站类型
    ,abnormal.abnormal_type_code           as abnormal_type_code
    ,abnormal.abnormal_type_name           as abnormal_type_name
    ,dim_qxt.city_id                       as duty_city_code                    --七星潭责任城市编码
    ,dim_qxt.city_desc                     as duty_city_name                    --七星潭责任城市名称
    ,dim_zy.city_id                        as ziyou_duty_city_code              --自有平台责任城市编码
    ,dim_zy.city_desc                      as ziyou_duty_city_name              --自有平台责任城市名称
    ,main.is_main_route                    as is_main_route                     --路由类型20231130新增
    ,main.end_center_shipment_no           as end_center_shipment_no            --目的中心任务单20231211新增
    ,main.ziyou_planned_departure_time     as ziyou_planned_departure_time      --自有平台计划发车时间 --20231226新增
    ,main.ziyou_planned_arrival_time       as ziyou_planned_arrival_time        --自有平台计划到车时间 --20231226新增
    ,to_date(aging_sign_time)              as dt
from (
    select
         waybill_no                                                                  --运单号'
        ,max(platform_effect                  ) as platform_effect                   --自有平台时效标准(兜底路由)
        ,max(ziyou_platform_effect            ) as ziyou_platform_effect             --自有平台时效标准(推荐路由)
        ,max(if_platform_standard             ) as if_platform_standard              --自有平台时效是否达成:1达成,0未达成(推荐路由)
        ,max(if_doudi_platform_standard       ) as if_doudi_platform_standard        --自有平台时效是否达成:1达成,0未达成(兜底路由)
        ,max(cainiao_platform_effect          ) as cainiao_platform_effect           --七星潭平台时效标准'
        ,max(if_cainiao_platform_pres_standard) as if_cainiao_platform_pres_standard --七星潭平台时效是否达成:1达成,0未达成'
        ,max(order_source_code                ) as order_source_code                 --订单来源编码
        ,max(order_source_name                ) as order_source_name                 --订单来源名称
        ,max(customer_code                    ) as customer_code                     --客户编码  20230824新增
        ,max(customer_name                    ) as customer_name                     --客户名称
        ,max(start_center_code                ) as start_center_code                 --始发中心编码
        ,max(start_center_name                ) as start_center_name                 --始发中心名称
        ,max(end_center_code                  ) as end_center_code                   --目的中心编码
        ,max(end_center_name                  ) as end_center_name                   --目的中心名称
        --七星潭
        ,max(duty_type_tmp) as duty_type --七星潭责任类型                                                                         --七星潭责任类型
        ,max(case when duty_type_tmp is not null then scan_site_code                 end) as duty_main_code                       --七星潭责任主体编码
        ,max(case when duty_type_tmp is not null then scan_site_name                 end) as duty_main_name                       --七星潭责任主体名称
        ,max(case when duty_type_tmp is not null then scan_site_type                 end) as duty_main_type                       --七星潭责任主体类型
        ,max(case when duty_type_tmp is not null then scan_site_actual_shipment_no   end) as duty_shipment_no                     --七星潭责任任务号
        ,max(case when duty_type_tmp is not null then scan_site_actual_shipment_name end) as duty_shipment_name                   --七星潭责任任务号名称
        ,max(case when duty_type_tmp is not null then scan_site_actual_send_time     end) as qxt_dynamic_scan_time                --七星潭实际扫描时间
        ,max(case when duty_type_tmp is not null then scan_site_plan_send_time       end) as qxt_static_scan_time                 --七星潭静态扫描时间
        ,max(case when duty_type_tmp is not null then scan_site_actual_arrival_time  end) as qxt_scan_site_actual_arrival_time    --七星潭当前站点实际到车时间
        ,max(case when duty_type_tmp is not null then scan_site_plan_arrival_time    end) as qxt_scan_site_plan_arrival_time      --七星潭当前站点规划到车时间
        ,max(case when duty_type_tmp is not null then scan_site_bagg_user_code       end) as duty_main_bagg_user_code             --七星潭责任主体建包扫描员编号
        ,max(case when duty_type_tmp is not null then scan_site_bagg_user_name       end) as duty_main_bagg_user_name             --七星潭责任主体建包扫描员名称
        ,max(case when duty_type_tmp is not null then pre_site_code                  end) as duty_main_pre_site_code              --七星潭责任主体上一站编码
        ,max(case when duty_type_tmp is not null then pre_site_name                  end) as duty_main_pre_site_name              --七星潭责任主体上一站名称
        ,max(case when duty_type_tmp is not null then pre_site_type                  end) as duty_main_pre_site_type              --七星潭责任主体上一站类型
        ,max(case when duty_type_tmp is not null then next_site_code                 end) as duty_main_next_site_code             --七星潭责任主体下一站编码
        ,max(case when duty_type_tmp is not null then next_site_name                 end) as duty_main_next_site_name             --七星潭责任主体下一站名称
        ,max(case when duty_type_tmp is not null then next_site_type                 end) as duty_main_next_site_type             --七星潭责任主体下一站类型
        --自有平台
        ,max(ziyou_duty_type_tmp) as ziyou_duty_type      --七星潭责任类型                                                          --自有平台责任类型
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_code                 end) as ziyou_duty_main_code             --自有平台责任主体编码
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_name                 end) as ziyou_duty_main_name             --自有平台责任主体名称
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_type                 end) as ziyou_duty_main_type             --自有平台责任主体类型
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_actual_shipment_no   end) as ziyou_duty_shipment_no           --自有平台责任任务号
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_actual_shipment_name end) as ziyou_duty_shipment_name         --自有平台责任任务号名称
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_actual_send_time     end) as zy_dynamic_scan_time             --自有平台实际扫描时间
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_plan_send_time       end) as zy_static_scan_time              --自有平台静态扫描时间
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_actual_arrival_time  end) as zy_scan_site_actual_arrival_time --自有平台当前站点实际到车时间
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_plan_arrival_time    end) as zy_scan_site_plan_arrival_time   --自有平台当前站点规划到车时间
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_bagg_user_code       end) as ziyou_duty_main_bagg_user_code   --自有平台责任主体建包扫描员编号
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_bagg_user_name       end) as ziyou_duty_main_bagg_user_name   --自有平台责任主体建包扫描员名称
        ,max(case when ziyou_duty_type_tmp is not null then pre_site_code                  end) as ziyou_duty_main_pre_site_code    --自有平台责任主体上一站编码
        ,max(case when ziyou_duty_type_tmp is not null then pre_site_name                  end) as ziyou_duty_main_pre_site_name    --自有平台责任主体上一站名称
        ,max(case when ziyou_duty_type_tmp is not null then pre_site_type                  end) as ziyou_duty_main_pre_site_type    --自有平台责任主体上一站类型
        ,max(case when ziyou_duty_type_tmp is not null then next_site_code                 end) as ziyou_duty_main_next_site_code   --自有平台责任主体下一站编码
        ,max(case when ziyou_duty_type_tmp is not null then next_site_name                 end) as ziyou_duty_main_next_site_name   --自有平台责任主体下一站名称
        ,max(case when ziyou_duty_type_tmp is not null then next_site_type                 end) as ziyou_duty_main_next_site_type   --自有平台责任主体下一站类型
        ,max(first_network_code    ) as first_network_code      --始发网点
        ,max(last_network_code     ) as last_network_code       --目的网点
        ,max(cainiao_plan_sign_time) as cainiao_plan_sign_time  --菜鸟规划签收时间
        ,max(ziyou_plan_sign_time  ) as ziyou_plan_sign_time    --自有平台规划签收时间
        ,max(aging_sign_time       ) as aging_sign_time         --时效签收时间
        ,max(actual_sign_time      ) as actual_sign_time        --实际签收时间
        ,max(is_main_route           ) as is_main_route            --路由类型20231130新增
        ,max(end_center_shipment_no  ) as end_center_shipment_no  --目的中心任务单 20231211新增
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_planned_departure_time end) as ziyou_planned_departure_time --自有平台计划发车时间 --20231226新增
        ,max(case when ziyou_duty_type_tmp is not null then scan_site_planned_arrival_time   end) as ziyou_planned_arrival_time   --自有平台计划到车时间 --20231226新增
    from (
        select
              *
             ,max(case when scan_site_code = end_center_code then nvl(scan_site_arrival_shipment_no,pre_site_send_shipment_no) end) over(partition by waybill_no order by scan_site_no desc)  as end_center_shipment_no  --目的中心任务单 20231211新增
             ,case when first_duty_type is not null and first_duty_type in ('静态路由缺失','静态慢于','地址解析错误','静态慢于(多班次)')
                   and scan_site_code = first_network_code then first_duty_type
                   when first_duty_type in ('乡镇加时') and scan_site_code = last_network_code then first_duty_type
                   else duty_type end as duty_type_tmp --七星潭责任
             ,case when first_ziyou_duty_type is not null and first_ziyou_duty_type in ('地址解析错误')
                   and scan_site_code = first_network_code then first_ziyou_duty_type
                   else ziyou_duty_type end as ziyou_duty_type_tmp --自有平台责任
             ,row_number() over(partition by waybill_no,scan_site_no order by scan_site_no) as rn --去重同城件
        from jms_dm.dm_whole_decide_duty_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',26) and date_add('{{ execution_date | cst_ds }}',5)
          and to_date(aging_sign_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
    ) a where a.rn = 1
         and (duty_type_tmp is not null or ziyou_duty_type_tmp is not null)
    group by waybill_no
) main 
left join jms_dim.dim_network_whole_massage dim_start
   on dim_start.code = main.first_network_code
left join jms_dim.dim_network_whole_massage dim_end
   on dim_end.code = main.last_network_code
left join jms_dim.dim_network_whole_massage dim_zy
   on dim_zy.code = main.ziyou_duty_main_code
left join jms_dim.dim_network_whole_massage dim_qxt
   on dim_qxt.code = main.duty_main_code
left join (
    select
          waybill_no
         ,type_code as abnormal_type_code  --拦截件登记时间
         ,type_desc as abnormal_type_name  --拦截件登记时间
    from (
        select
             *,row_number() over (partition by waybill_no order by scan_time asc) as rn
        from jms_dwd.dwd_wide_abnormal_union_waybill_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',45) and '{{ execution_date | cst_ds }}'
        and scan_type = 'difficult'
    ) a where a.rn = 1
) abnormal on main.waybill_no = abnormal.waybill_no
distribute by dt,pmod(hash(rand()),50)
;



